﻿using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace ESAM_Assignment_3
{
    public class CommentClass
    {
        #region Attributes
        private int commentID;
        private string comment;
        private string datePosted;
        private string subject;
        private string modified;
        private string fullname; //To store the user's Fullname
        private string username; //To store the user's Username
        #endregion

        #region Properties | GET and SET methods
        public int CommentID
        {
            get { return commentID; }
            set { commentID = value; }
        }
        public string Comment
        {
            get { return comment; }
            set { comment = value; }
        }
        public string DatePosted
        {
            get { return datePosted; }
            set { datePosted = value; }
        }
        public string Subject
        {
            get { return subject; }
            set { subject = value; }
        }
        public string Modified
        {
            get { return modified; }
            set { modified = value; }
        }
        public string Fullname
        {
            get { return fullname; }
            set { fullname = value; }
        }
        public string Username
        {
            get { return username; }
            set { username = value; }
        }

        #endregion

        #region Constructor
        /// <summary>
        /// Generic class constructor
        /// </summary>
        public CommentClass()
        {
        }
        #endregion

        private static string connectionString = "data source=localhost; initial catalog=" + Common.Database_Name + "; integrated security=false; user id=sa; password=imsa";

        /// <summary>
        /// Method to convert the comment's SQLDateTime to a String format. 
        /// </summary>
        /// <returns></returns>
        private static string ConvertSQLDateTimeToString(System.Data.SqlTypes.SqlDateTime CommentSQLDateTime)
        {
            return Convert.ToDateTime(CommentSQLDateTime.ToString()).ToString("dddd, dd-MMM-yyyy hh:mm tt");
        }

        /// <summary>
        /// Method to retrieve all comments from the table: Comments
        /// </summary>
        /// <returns></returns>
        public static ArrayList ShowComments()
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            ArrayList result = new ArrayList();

            try
            {
                myConn.ConnectionString = connectionString;
                myConn.Open();

                strQuery += "SELECT c.CommentID, c.Comment, c.Username, c.DatePosted, c.Subject, c.Modified, u.Fullname ";
                strQuery += "FROM Comments AS c INNER JOIN Users AS u ON c.Username = u.Username";
                myCmd = new SqlCommand(strQuery, myConn);
                SqlDataReader dr = myCmd.ExecuteReader();
                while (dr.Read())
                {
                    CommentClass myComment = new CommentClass();

                    myComment.CommentID = dr.GetInt32(0); //CommentID
                    myComment.Comment = dr.GetString(1); //Comment
                    myComment.Username = dr.GetString(2); //Username
                    myComment.DatePosted = ConvertSQLDateTimeToString(dr.GetSqlDateTime(3)); //DatePosted
                    myComment.Subject = dr.GetString(4); //Subject
                    myComment.Modified = dr.GetString(5); //Modified
                    myComment.Fullname = dr.GetString(6); //Fullname
                    result.Add(myComment);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }

        /// <summary>
        /// Method to retrieve the details of the comment given the CommentID.
        /// </summary>
        /// <param name="CommentID"></param>
        /// <returns></returns>
        public static CommentClass GetComment(int CommentID)
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            CommentClass result = new CommentClass();

            try
            {
                myConn.ConnectionString = connectionString;
                myConn.Open();

                strQuery += "SELECT c.CommentID, c.Comment, c.Username, c.DatePosted, c.Subject, u.Fullname ";
                strQuery += "FROM Comments AS c INNER JOIN ";
                strQuery += "     Users AS u ON c.Username = u.Username ";
                strQuery += "WHERE CommentID=" + CommentID.ToString();
                myCmd = new SqlCommand(strQuery, myConn);

                SqlDataReader dr = myCmd.ExecuteReader();
                while (dr.Read())
                {
                    result.CommentID = dr.GetInt32(0); //CommentID
                    result.Comment = dr.GetString(1); //Comment
                    result.Username = dr.GetString(2); //Username
                    result.DatePosted = ConvertSQLDateTimeToString(dr.GetSqlDateTime(3)); //DatePosted
                    result.Subject = dr.GetString(4); //Subject
                    result.Fullname = dr.GetString(5); //Fullname
                 }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }

        /// <summary>
        /// Method to insert a new comment. Modified status will automatically be set.
        /// </summary>
        /// <param name="Comment"></param>
        /// <param name="UserID"></param>
        /// <param name="DatePosted"></param>
        /// <param name="Subject"></param>
        /// <returns></returns>
        public static bool InsertComment(string Comment, string Username, string DatePosted, string Subject)
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //Create the tables in the newly create database
            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery += "INSERT INTO Comments (Comment, Username, DatePosted, Subject, Modified) VALUES (@Comment, @Username, @DatePosted, @Subject, 'n')";             
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.Parameters.AddWithValue("@Comment", Comment);
                myCmd.Parameters.AddWithValue("@Username", Username);
                myCmd.Parameters.AddWithValue("@DatePosted", DatePosted);
                myCmd.Parameters.AddWithValue("@Subject", Subject);                
                myCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// Method to update an existing comment. Modified status will automatically be set.
        /// </summary>
        /// <param name="CommentID"></param>
        /// <param name="Comment"></param>
        /// <param name="DatePosted"></param>
        /// <param name="Subject"></param>
        /// <returns></returns>
        public static bool UpdateComment(int CommentID, string Comment, string DatePosted, string Subject)
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //Create the tables in the newly create database
            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery += "UPDATE Comments SET Comment=@Comment, DatePosted=@DatePosted, Subject=@Subject, Modified='y' WHERE CommentID=@CommentID";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.Parameters.AddWithValue("@Comment", Comment);
                myCmd.Parameters.AddWithValue("@DatePosted", DatePosted);
                myCmd.Parameters.AddWithValue("@Subject", Subject);
                myCmd.Parameters.AddWithValue("@CommentID", CommentID.ToString());
                myCmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                myConn.Close();
            }
        }

        /// <summary>
        /// Method to delete a comment given the CommentID.
        /// </summary>
        /// <param name="CommentID"></param>
        /// <returns></returns>
        public static bool DeleteComment(int CommentID)
        {
            String strQuery = "";
            SqlConnection myConn = new SqlConnection();
            SqlCommand myCmd;

            //Create the tables in the newly create database
            try
            {
                myConn = new SqlConnection(connectionString);
                myConn.Open();

                strQuery += "DELETE Comments WHERE CommentID=@CommentID";
                myCmd = new SqlCommand(strQuery, myConn);
                myCmd.Parameters.AddWithValue("@CommentID", CommentID.ToString());
                int row = myCmd.ExecuteNonQuery();

                return (row == 1);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                myConn.Close();
            }
        }

    }
}